--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_TERM_LOAB.HQL
--    Functions : 表18：贷款余额分期限统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_TERM_LOAB PARTITION (DATA_DATE = '#V_DATA_DATE#')
SELECT  AREA.AREA_CODE_4              AS FIN_ORG_DIST
       ,T.FIN_ORG_NO                  AS FIN_ORG_NO
       ,T.CCY                         AS CCY
       ,T.TERM_CODE                   AS TERM_CODE
       ,T.TERM_DESC                   AS TERM_DESC
       ,SUM(T.ACTUAL_BAL)/100000000   AS ACTUAL_BAL      -- 贷款余额
       ,SUM(T.BAL_LM    )/100000000   AS BAL_LM          -- 余额上期末
       ,SUM(T.BAL_YF    )/100000000   AS BAL_YF          -- 余额年初 0101
       ,SUM(T.BAL_GROW  )/100000000   AS BAL_GROW        -- 同比增长
       ,SUM(T.WSUM_BAL  )/100000000   AS WSUM_BAL        -- 余额加权值
       ,SUM(T.WBAL_LM   )/100000000   AS WBAL_LM         -- 余额加权值上期末
       ,SUM(T.WBAL_YF   )/100000000   AS WBAL_YF         -- 余额加权值年初
       ,SUM(T.WBAL_LC   )/100000000   AS WBAL_LC         -- 余额加权值去年同期
FROM ( 
      -- 分期限
      SELECT
       YE.FIN_ORG_NO                                                AS FIN_ORG_NO
      ,YE.CCY                                                       AS CCY
      ,TERM.LOAN_TERM_ID                                            AS TERM_CODE     -- 期限代码
      ,TERM.LOAN_TERM_DSCR                                          AS TERM_DESC     -- 期限描述
      ,COALESCE(YE.ACTUAL_BAL,0)                                    AS ACTUAL_BAL    -- 贷款余额
      ,COALESCE(YE.BAL_LM,0)                                        AS BAL_LM        -- 余额上期末
      ,COALESCE(YE.BAL_YF,0)                                        AS BAL_YF        -- 余额年初 0101
      ,COALESCE(YE.ACTUAL_BAL,0) - COALESCE(YE.BAL_LC,0)            AS BAL_GROW      -- 同比增长
      ,COALESCE(YE.WSUM_BAL,0)                                      AS WSUM_BAL      -- 余额加权值
      ,COALESCE(YE.WBAL_LM,0)                                       AS WBAL_LM       -- 余额加权值上期末
      ,COALESCE(YE.WBAL_YF,0)                                       AS WBAL_YF       -- 余额加权值年初
      ,COALESCE(YE.WBAL_LC,0)                                       AS WBAL_LC       -- 余额加权值去年同期
      FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE DATA_DATE='#V_DATA_DATE#') YE
      LEFT JOIN DIMENSION.DIM_LOAN_TERM TERM ON YE.TERM_CODE=TERM.LOAN_TERM_ID AND '#V_DATA_DATE#' BETWEEN TERM.START_DATE AND TERM.END_DATE
      
      UNION ALL
      -- 所有期限 有期限+无期限
      SELECT
       YE.FIN_ORG_NO                                                AS FIN_ORG_NO
      ,YE.CCY                                                       AS CCY
      ,'Z'                                                          AS TERM_CODE     -- 期限代码
      ,'所有期限'                                                   AS TERM_DESC     -- 期限描述
      ,COALESCE(YE.ACTUAL_BAL,0)                                    AS ACTUAL_BAL    -- 贷款余额
      ,COALESCE(YE.BAL_LM,0)                                        AS BAL_LM        -- 余额上期末
      ,COALESCE(YE.BAL_YF,0)                                        AS BAL_YF        -- 余额年初 0101
      ,COALESCE(YE.ACTUAL_BAL,0) - COALESCE(YE.BAL_LC,0)            AS BAL_GROW      -- 同比增长
      ,COALESCE(YE.WSUM_BAL,0)                                      AS WSUM_BAL      -- 余额加权值
      ,COALESCE(YE.WBAL_LM,0)                                       AS WBAL_LM       -- 余额加权值上期末
      ,COALESCE(YE.WBAL_YF,0)                                       AS WBAL_YF       -- 余额加权值年初
      ,COALESCE(YE.WBAL_LC,0)                                       AS WBAL_LC       -- 余额加权值去年同期
      FROM (SELECT * FROM EDW.DS_LOAB_SUM WHERE DATA_DATE='#V_DATA_DATE#') YE
   )T
   -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
   GROUP BY  AREA.AREA_CODE_4
            ,T.FIN_ORG_NO
            ,T.CCY
            ,T.TERM_CODE
            ,T.TERM_DESC
;